package com.ef.dao.impl;

import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

import org.hibernate.SQLQuery;
import org.hibernate.criterion.DetachedCriteria;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.Restrictions;

import com.ef.constant.EClassType;
import com.ef.constant.ELeadsClassStatus;
import com.ef.constant.ETimeline;
import com.ef.constant.StatusType;
import com.ef.dao.LeadsClassEntityDao;
import com.ef.domain.ClassEntity;
import com.ef.domain.Leads;
import com.ef.domain.LeadsClassEntity;
import com.ef.util.CalendarUtil;
import com.ef.web.domain.TimeCourseCount;
import com.ef.web.domain.TodayCourseCount;

public class LeadsClassEntityDaoImpl extends DaoImpl<LeadsClassEntity>
		implements LeadsClassEntityDao {

	public List<LeadsClassEntity> getLeadsClassEntityListOrderByBookingTime(
			int year, int week, Leads leads) {
		DetachedCriteria criterion = DetachedCriteria
				.forClass(LeadsClassEntity.class);
		Date monday = CalendarUtil.getMondayOfWeekWithMonDayIsFirstDay(year,
				week);
		Date sunday = CalendarUtil.getSundayOfWeekWithMonDayIsFirstDay(year,
				week);
		criterion.createCriteria("leads").add(
				Restrictions.eq("id", leads.getId()));
		criterion.createCriteria("classEntity", "classEntity").add(
				Restrictions.ge("classDate", monday)).add(
				Restrictions.le("classDate", sunday)).add(
				Restrictions.eq("status", StatusType.VALID));
		criterion.addOrder(Order.desc("bookingTime"));
		return findAllByCriteria(criterion);
	}

	public List<LeadsClassEntity> getLeadsClassEntityListOrderByBookingTime(
			Leads leads, int num) {
		DetachedCriteria criterion = DetachedCriteria
				.forClass(LeadsClassEntity.class);
		criterion.createCriteria("leads").add(
				Restrictions.eq("id", leads.getId()));
		criterion.createCriteria("classEntity", "classEntity").add(
				Restrictions.eq("status", StatusType.VALID));
		criterion.addOrder(Order.desc("classEntity.classDate")).addOrder(
				Order.asc("classEntity.classTime"));
		return this.findAllByCriteria(criterion,num);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * com.ef.dao.LeadsClassEntityDao#getLeadsClassEntityListWithClassEntity
	 * (com.ef.domain.ClassEntity)
	 */
	public List<LeadsClassEntity> getLeadsClassEntityListWithClassEntity(
			ClassEntity classEntity) {
		DetachedCriteria criterion = DetachedCriteria
				.forClass(LeadsClassEntity.class);
		criterion.createCriteria("classEntity").add(
				Restrictions.eq("id", classEntity.getId()));
		criterion.addOrder(Order.asc("classStatus"));
		criterion.createCriteria("leads").addOrder(Order.asc("eliteCard"));
//		criterion.addOrder(Order.asc("status"));
//		criterion.addOrder(Order.asc("bookingTime"));
//		criterion.createAlias("leads", "leads");
//		criterion.addOrder(Order.asc("leads.eliteCard"));
		List<LeadsClassEntity> leadsClassList = findAllByCriteria(criterion);
		return leadsClassList;
	}
	
	public List<LeadsClassEntity> getLeadsClassEntityListWithClassEntityOrderByElitCard(
			ClassEntity classEntity) {
		DetachedCriteria criterion = DetachedCriteria
				.forClass(LeadsClassEntity.class);
		criterion.createCriteria("classEntity").add(
				Restrictions.eq("id", classEntity.getId()));
//		criterion.addOrder(Order.asc("bookingTime"));
 		criterion.createAlias("leads", "leads");
 		criterion.addOrder(Order.asc("leads.eliteCard"));
		List<LeadsClassEntity> leadsClassList = findAllByCriteria(criterion);
		return leadsClassList;
	}

	public LeadsClassEntity getLeadsClassEntityWithClassEntity(
			Long classEntityid, Long leadsid) {
		DetachedCriteria criterion = DetachedCriteria
				.forClass(LeadsClassEntity.class);
		criterion.createCriteria("classEntity").add(
				Restrictions.eq("id", classEntityid));
		criterion.createCriteria("leads").add(Restrictions.eq("id", leadsid));
		criterion.addOrder(Order.desc("bookingTime"));
		return findAllByCriteria(criterion).get(0);
	}

	public List<LeadsClassEntity> getLeadsClassEntityList(Leads leads, int max) {
		DetachedCriteria criterion = DetachedCriteria
				.forClass(LeadsClassEntity.class);
		criterion.createCriteria("leads").add(
				Restrictions.eq("id", leads.getId()));
		criterion.addOrder(Order.desc("bookingTime"));
		return findAllByCriteria(criterion, max);
	}

	public boolean leadsHasThisClassEntity(Long classEntityid, Long leadsid) {
		DetachedCriteria criterion = DetachedCriteria
				.forClass(LeadsClassEntity.class);
		// criterion.createAlias(alias+".vouchingStruct",vouchingStruct)
		criterion.createCriteria("classEntity").add(
				Restrictions.eq("id", classEntityid));
		criterion.createCriteria("leads").add(Restrictions.eq("id", leadsid));
		// 不为Cancel, Cancel还是允许订课
		criterion.add(Restrictions.ne("classStatus", ELeadsClassStatus.CANCEL));
		criterion.add(Restrictions.ne("classStatus", ELeadsClassStatus.CLEAR));
		List<LeadsClassEntity> leadsClassList = findAllByCriteria(criterion);
		if (leadsClassList != null && leadsClassList.size() > 0) {
			return true;
		} else {
			return false;
		}
	}

	public TodayCourseCount countTodayCourseInfo(Date today) {
		SimpleDateFormat formater = new SimpleDateFormat("yyyy-MM-dd");

		TodayCourseCount countResult = new TodayCourseCount();
		// 1.当天总提供的课量节数
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT COUNT(*) AS c FROM ef_classentity t ");
		sql
				.append("WHERE t.status='VALID' AND to_char(t.classdate,'yyyy-mm-dd') = '");
		sql.append(formater.format(today) + "'");

		SQLQuery query = this.getSession().createSQLQuery(sql.toString());
		List l = query.list();
		BigDecimal o = (BigDecimal) l.get(0);
		int count = o.intValue();

		// 2.当天订课满员的课堂节数
		StringBuilder sql2 = new StringBuilder();
		sql2.append("SELECT A.wk + B.lc + C.f2f FROM (");

		sql2
				.append("SELECT COUNT(*) AS wk  FROM ef_classentity t,ef_classtype l ");
		sql2
				.append("WHERE t.classtype_id = l.id AND l.type = 'WK'  AND t.bookingcount >= 25 AND ");
		sql2.append(" to_char(t.classdate,'yyyy-mm-dd') = '");
		sql2.append(formater.format(today) + "') A, (");

		sql2
				.append("SELECT COUNT(*) AS lc  FROM ef_classentity t,ef_classtype l ");
		sql2
				.append("WHERE t.classtype_id = l.id AND l.type = 'LC'  AND   t.bookingcount >= 15 AND ");
		sql2.append(" to_char(t.classdate,'yyyy-mm-dd') = ' ");
		sql2.append(formater.format(today) + "') B, (");

		sql2
				.append("SELECT COUNT(*) AS f2f  FROM ef_classentity t,ef_classtype l ");
		sql2
				.append("WHERE t.classtype_id = l.id AND l.type = 'F2F'  AND   t.bookingcount >= 4 AND ");
		sql2.append(" to_char(t.classdate,'yyyy-mm-dd') = ' ");
		sql2.append(formater.format(today) + "') C ");

		SQLQuery sq = this.getSession().createSQLQuery(sql2.toString());
		List l2 = sq.list();
		BigDecimal o2 = (BigDecimal) l2.get(0);
		int count2 = o2.intValue();

		// 3.当天总共等待的学员数量（人数）
		StringBuilder sql3 = new StringBuilder();
		sql3.append("SELECT COUNT(*) AS c FROM ef_leadsclassentity t WHERE ");
		sql3.append(" (t.classStatus = 'WAITING' OR t.classStatus = 'CLEAR')  AND ");
		sql3.append(" to_char(t.bookingTime,'yyyy-mm-dd') = '");
		sql3.append(formater.format(today) + "'");

		SQLQuery query3 = this.getSession().createSQLQuery(sql3.toString());
		List l3 = query3.list();
		BigDecimal o3 = (BigDecimal) l3.get(0);
		int count3 = o3.intValue();

		// 4.课堂上实际到课的人数
		StringBuilder sql4 = new StringBuilder();
		sql4.append("SELECT COUNT(*) AS c FROM ef_leadsclassentity t WHERE ");
		sql4.append(" t.classStatus = 'CHECKIN' AND ");
		sql4.append(" to_char(t.checkinTime,'yyyy-mm-dd') = '");
		sql4.append(formater.format(today) + "'");

		SQLQuery query4 = this.getSession().createSQLQuery(sql4.toString());
		List l4 = query4.list();
		BigDecimal o4 = (BigDecimal) l4.get(0);
		int count4 = o4.intValue();

		// 5.总的订课人数
		StringBuilder sql5 = new StringBuilder();
		sql5.append("SELECT COUNT(*) AS c FROM ef_leadsclassentity t WHERE ");
		// sql5.append(" t.classStatus = 'CHECKIN' AND ");
		sql5.append(" to_char(t.bookingTime,'yyyy-mm-dd') = '");
		sql5.append(formater.format(today) + "'");

		SQLQuery query5 = this.getSession().createSQLQuery(sql5.toString());
		List l5 = query5.list();
		BigDecimal o5 = (BigDecimal) l5.get(0);
		int count5 = o5.intValue();

		countResult.setAvailableCourseCount(count);
		countResult.setFullCourseCount(count2);
		countResult.setWaitingStudents(count3);
		countResult.setCheckinStudents(count4);
		countResult.setAllBookingStudents(count5);
		countResult.setToday(today);

		return countResult;
	}

	/**
	 * 
	 * @param today
	 * @param type
	 *            F2F , WK , LC 三种
	 * @return
	 */
	public TodayCourseCount countTodaySpecialCourseInfo(Date today,
			EClassType type) {
		SimpleDateFormat formater = new SimpleDateFormat("yyyy-MM-dd");
		TodayCourseCount countResult = new TodayCourseCount();
		// 1.当天总提供的课量节数
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT COUNT(*) FROM ef_classentity t,ef_classtype l ");
		sql.append("WHERE to_char(t.classdate,'yyyy-mm-dd') = '");
		sql.append(formater.format(today) + "' AND ");
		sql.append(" t.classtype_id = l.id AND  t.status='VALID'  AND   l.type= '");
		sql.append(type).append("' ");

		SQLQuery query = this.getSession().createSQLQuery(sql.toString());
		List l = query.list();
		BigDecimal o = (BigDecimal) l.get(0);
		int count = o.intValue();

		// 2.当天订课满员的课堂节数
		int fullCount = 0;

		if (type.equals(EClassType.F2F)) {
			fullCount = 4;
		} else if (type.equals(EClassType.WK)) {
			fullCount = 25;
		} else if (type.equals(EClassType.LC)) {
			fullCount = 15;
		}

		StringBuilder sql2 = new StringBuilder();

		sql2.append("SELECT COUNT(*)   ");
		sql2.append(" FROM ef_classentity t,ef_classtype l ");
		sql2
				.append(
						" WHERE t.type='NORMAL' AND t.status='VALID' AND t.classtype_id = l.id AND l.type = '")
				.append(type).append("'  AND t.bookingcount >=").append(
						fullCount);
		sql2.append(" AND to_char(t.classdate,'yyyy-mm-dd') = '");
		sql2.append(formater.format(today) + "' ");

		SQLQuery query2 = this.getSession().createSQLQuery(sql2.toString());
		List l2 = query2.list();
		BigDecimal o2 = (BigDecimal) l2.get(0);
		int count2 = o2.intValue();

		// 3.当天总共等待的学员数量（人数）
		StringBuilder sql3 = new StringBuilder();
		sql3
				.append("SELECT COUNT(*) FROM ef_leadsclassentity t,ef_classentity t2,ef_classtype t3 WHERE ");
		sql3
				.append("  (t.classStatus = 'WAITING' OR t.classStatus = 'CLEAR')  AND t.classentity_id = t2.id AND t2.status='VALID' AND t2.classtype_id = t3.id AND ");
		sql3.append(" to_char(t2.classdate,'yyyy-mm-dd') = '");
		sql3.append(formater.format(today) + "'");
		sql3.append(" AND t3.type = '").append(type).append("' ");

		SQLQuery query3 = this.getSession().createSQLQuery(sql3.toString());
		List l3 = query3.list();
		BigDecimal o3 = (BigDecimal) l3.get(0);
		int count3 = o3.intValue();

		// 4.课堂上实际到课的人数
		StringBuilder sql4 = new StringBuilder();
		sql4
				.append("SELECT COUNT(*) FROM ef_leadsclassentity t,ef_classentity t2,ef_classtype t3 WHERE ");
		sql4
				.append(" t.classStatus = 'CHECKIN' AND t.classentity_id = t2.id AND t2.status='VALID' AND t2.classtype_id = t3.id AND ");
		sql4.append(" to_char(t2.classdate,'yyyy-mm-dd') = '");
		sql4.append(formater.format(today) + "'");
		sql4.append(" AND t3.type = '").append(type).append("' ");

		SQLQuery query4 = this.getSession().createSQLQuery(sql4.toString());
		List l4 = query4.list();
		BigDecimal o4 = (BigDecimal) l4.get(0);
		int count4 = o4.intValue();

		// 5.总的订课人数
		StringBuilder sql5 = new StringBuilder();
		sql5
				.append("SELECT COUNT(*) FROM ef_leadsclassentity t,ef_classentity t2,ef_classtype t3 WHERE ");
		sql5
				.append(" t.classentity_id = t2.id AND t2.classtype_id = t3.id AND t2.status='VALID'  AND t.classStatus != 'CANCEL' AND ");
		sql5.append(" to_char(t2.classdate,'yyyy-mm-dd') = '");
		sql5.append(formater.format(today) + "'");
		sql5.append(" AND t3.type = '").append(type).append("' ");

		SQLQuery query5 = this.getSession().createSQLQuery(sql5.toString());
		List l5 = query5.list();
		BigDecimal o5 = (BigDecimal) l5.get(0);
		int count5 = o5.intValue();

		// 6.Waiting后booking 上的人数
		StringBuilder sql6 = new StringBuilder();
		sql6
				.append("SELECT COUNT(*) FROM ef_leadsclassentity t,ef_classentity t2,ef_classtype t3 WHERE ");
		sql6
				.append(" t.classentity_id = t2.id AND t2.classtype_id = t3.id  AND t2.status='VALID' AND bookingfromwaiting =1  AND ");
		sql6.append(" to_char(t2.classdate,'yyyy-mm-dd') = '");
		sql6.append(formater.format(today) + "'");
		sql6.append(" AND t3.type = '").append(type).append("' ");

		SQLQuery query6 = this.getSession().createSQLQuery(sql6.toString());
		List l6 = query6.list();
		BigDecimal o6 = (BigDecimal) l6.get(0);
		int count6 = o6.intValue();

		countResult.setAvailableCourseCount(count);
		countResult.setFullCourseCount(count2);
		countResult.setWaitingStudents(count3);
		countResult.setCheckinStudents(count4);
		countResult.setAllBookingStudents(count5);
		countResult.setWaitingForClassStudents(count6);
		countResult.setToday(today);

		return countResult;
	}

	public TimeCourseCount countByTimeTeacher(Date today, ETimeline classTime) {
		TimeCourseCount countResult = new TimeCourseCount();
		SimpleDateFormat formater = new SimpleDateFormat("yyyy-MM-dd");
		StringBuilder sql = new StringBuilder();
		// F2F
		sql
				.append("SELECT COUNT(*) FROM ef_classentity e,ef_teacher t,ef_classtype k WHERE  ");
		sql
				.append(" e.status='VALID' AND e.teacher_id = t.id  AND e.classtype_id = k.id AND ");
		sql.append(" to_char(e.classDate,'yyyy-mm-dd') = '").append(
				formater.format(today)).append("' ");
		sql.append(" AND e.classTime = '").append(classTime.name()).append(
				"' AND ");
		sql.append(" t.foreign = 1  AND k.type = '").append(EClassType.F2F)
				.append("' ");

		SQLQuery query = this.getSession().createSQLQuery(sql.toString());
		List list = query.list();
		BigDecimal c = (BigDecimal) list.get(0);
		int count = c.intValue();
		countResult.setFfcount(count);

		StringBuilder sql2 = new StringBuilder();
		sql2
				.append("SELECT COUNT(*) FROM ef_classentity e,ef_teacher t,ef_classtype k WHERE  ");
		sql2
				.append(" e.status='VALID' AND e.teacher_id = t.id  AND e.classtype_id = k.id AND ");
		sql2.append(" to_char(e.classDate,'yyyy-mm-dd') = '").append(
				formater.format(today)).append("' ");
		sql2.append(" AND e.classTime = '").append(classTime.name()).append(
				"' AND ");
		sql2.append(" t.foreign = 0  AND k.type = '").append(EClassType.F2F)
				.append("' ");

		SQLQuery query2 = this.getSession().createSQLQuery(sql2.toString());
		List list2 = query2.list();
		BigDecimal c2 = (BigDecimal) list2.get(0);
		int count2 = c2.intValue();
		countResult.setFccount(count2);

		// WK
		StringBuilder sql3 = new StringBuilder();
		sql3
				.append("SELECT COUNT(*) FROM ef_classentity e,ef_teacher t,ef_classtype k WHERE  ");
		sql3
				.append(" e.status='VALID' AND e.teacher_id = t.id  AND e.classtype_id = k.id AND ");
		sql3.append(" to_char(e.classDate,'yyyy-mm-dd') = '").append(
				formater.format(today)).append("' ");
		sql3.append(" AND e.classTime = '").append(classTime.name()).append(
				"' AND ");
		sql3.append(" t.foreign = 1  AND k.type = '").append(EClassType.WK)
				.append("' ");

		SQLQuery query3 = this.getSession().createSQLQuery(sql3.toString());
		List list3 = query3.list();
		BigDecimal c3 = (BigDecimal) list3.get(0);
		int count3 = c3.intValue();
		countResult.setWfcount(count3);

		StringBuilder sql4 = new StringBuilder();
		sql4
				.append("SELECT COUNT(*) FROM ef_classentity e,ef_teacher t,ef_classtype k WHERE  ");
		sql4
				.append(" e.status='VALID' AND e.teacher_id = t.id  AND e.classtype_id = k.id AND ");
		sql4.append(" to_char(e.classDate,'yyyy-mm-dd') = '").append(
				formater.format(today)).append("' ");
		sql4.append(" AND e.classTime = '").append(classTime.name()).append(
				"' AND ");
		sql4.append(" t.foreign = 0  AND k.type = '").append(EClassType.WK)
				.append("' ");

		SQLQuery query4 = this.getSession().createSQLQuery(sql4.toString());
		List list4 = query4.list();
		BigDecimal c4 = (BigDecimal) list4.get(0);
		int count4 = c4.intValue();
		countResult.setWccount(count4);

		// LC
		StringBuilder sql5 = new StringBuilder();
		sql5
				.append("SELECT COUNT(*) FROM ef_classentity e,ef_teacher t,ef_classtype k WHERE  ");
		sql5
				.append(" e.status='VALID' AND e.teacher_id = t.id  AND e.classtype_id = k.id AND ");
		sql5.append(" to_char(e.classDate,'yyyy-mm-dd') = '").append(
				formater.format(today)).append("' ");
		sql5.append(" AND e.classTime = '").append(classTime.name()).append(
				"' AND ");
		sql5.append(" t.foreign = 1  AND k.type = '").append(EClassType.LC)
				.append("' ");

		SQLQuery query5 = this.getSession().createSQLQuery(sql5.toString());
		List list5 = query5.list();
		BigDecimal c5 = (BigDecimal) list5.get(0);
		int count5 = c5.intValue();
		countResult.setLfcount(count5);

		StringBuilder sql6 = new StringBuilder();
		sql6
				.append("SELECT COUNT(*) FROM ef_classentity e,ef_teacher t,ef_classtype k WHERE  ");
		sql6
				.append(" e.status='VALID' AND e.teacher_id = t.id  AND e.classtype_id = k.id AND ");
		sql6.append(" to_char(e.classDate,'yyyy-mm-dd') = '").append(
				formater.format(today)).append("' ");
		sql6.append(" AND e.classTime = '").append(classTime.name()).append(
				"' AND ");
		sql6.append(" t.foreign = 0  AND k.type = '").append(EClassType.LC)
				.append("' ");

		SQLQuery query6 = this.getSession().createSQLQuery(sql6.toString());
		List list6 = query6.list();
		BigDecimal c6 = (BigDecimal) list6.get(0);
		int count6 = c6.intValue();
		countResult.setLccount(count6);

		countResult.setToday(today);
		countResult.setTime(classTime);

		return countResult;

	}

	public TimeCourseCount countStudentsByTime(Date today, ETimeline classTime) {
		TimeCourseCount countResult = new TimeCourseCount();
		SimpleDateFormat formater = new SimpleDateFormat("yyyy-MM-dd");
		StringBuilder sql = new StringBuilder();
		// F2F
		sql
				.append("SELECT COUNT(*) FROM ef_leadsclassentity h, ef_classentity e,ef_teacher t,ef_classtype k WHERE  ");
		sql
				.append("e.status='VALID' AND e.teacher_id = t.id  AND e.classtype_id = k.id AND  h.classentity_id = e.id AND ");
		sql
				.append(
						" h.classStatus = 'CHECKIN' AND to_char(e.classDate,'yyyy-mm-dd') = '")
				.append(formater.format(today)).append("'    ");
		sql.append(" AND e.classTime = '").append(classTime.name()).append(
				"' AND ");
		sql.append(" t.foreign = 1  AND k.type = '").append(EClassType.F2F)
				.append("' ");

		SQLQuery query = this.getSession().createSQLQuery(sql.toString());
		List list = query.list();
		BigDecimal c = (BigDecimal) list.get(0);
		int count = c.intValue();
		countResult.setFfcount(count);

		StringBuilder sql2 = new StringBuilder();
		sql2
				.append("SELECT COUNT(*) FROM  ef_leadsclassentity h, ef_classentity e,ef_teacher t,ef_classtype k WHERE  ");
		sql2
				.append(" e.status='VALID' AND e.teacher_id = t.id  AND e.classtype_id = k.id AND h.classentity_id = e.id AND ");
		sql2
				.append(
						" h.classStatus = 'CHECKIN' AND  to_char(e.classDate,'yyyy-mm-dd') = '")
				.append(formater.format(today)).append("'    ");
		sql2.append(" AND e.classTime = '").append(classTime.name()).append(
				"' AND ");
		sql2.append(" t.foreign = 0  AND k.type = '").append(EClassType.F2F)
				.append("' ");

		SQLQuery query2 = this.getSession().createSQLQuery(sql2.toString());
		List list2 = query2.list();
		BigDecimal c2 = (BigDecimal) list2.get(0);
		int count2 = c2.intValue();
		countResult.setFccount(count2);

		// WK
		StringBuilder sql3 = new StringBuilder();
		sql3
				.append("SELECT COUNT(*) FROM  ef_leadsclassentity h, ef_classentity e,ef_teacher t,ef_classtype k WHERE  ");
		sql3
				.append(" e.status='VALID' AND e.teacher_id = t.id  AND e.classtype_id = k.id AND h.classentity_id = e.id AND ");
		sql3
				.append(
						"h.classStatus = 'CHECKIN' AND to_char(e.classDate,'yyyy-mm-dd') = '")
				.append(formater.format(today)).append("'    ");
		sql3.append(" AND e.classTime = '").append(classTime.name()).append(
				"' AND ");
		sql3.append(" t.foreign = 1  AND k.type = '").append(EClassType.WK)
				.append("' ");

		SQLQuery query3 = this.getSession().createSQLQuery(sql3.toString());
		List list3 = query3.list();
		BigDecimal c3 = (BigDecimal) list3.get(0);
		int count3 = c3.intValue();
		countResult.setWfcount(count3);

		StringBuilder sql4 = new StringBuilder();
		sql4
				.append("SELECT COUNT(*) FROM ef_leadsclassentity h, ef_classentity e,ef_teacher t,ef_classtype k WHERE  ");
		sql4
				.append("e.status='VALID' AND  e.teacher_id = t.id  AND e.classtype_id = k.id AND h.classentity_id = e.id AND ");
		sql4
				.append(
						"h.classStatus = 'CHECKIN' AND  to_char(e.classDate,'yyyy-mm-dd') = '")
				.append(formater.format(today)).append("'    ");
		sql4.append(" AND e.classTime = '").append(classTime.name()).append(
				"' AND ");
		sql4.append(" t.foreign = 0  AND k.type = '").append(EClassType.WK)
				.append("' ");

		SQLQuery query4 = this.getSession().createSQLQuery(sql4.toString());
		List list4 = query4.list();
		BigDecimal c4 = (BigDecimal) list4.get(0);
		int count4 = c4.intValue();
		countResult.setWccount(count4);

		// LC
		StringBuilder sql5 = new StringBuilder();
		sql5
				.append("SELECT COUNT(*) FROM  ef_leadsclassentity h, ef_classentity e,ef_teacher t,ef_classtype k WHERE  ");
		sql5
				.append(" e.status='VALID' AND e.teacher_id = t.id  AND e.classtype_id = k.id AND h.classentity_id = e.id AND ");
		sql5
				.append(
						"h.classStatus = 'CHECKIN' AND  to_char(e.classDate,'yyyy-mm-dd') = '")
				.append(formater.format(today)).append("'    ");
		sql5.append(" AND e.classTime = '").append(classTime.name()).append(
				"' AND ");
		sql5.append(" t.foreign = 1  AND k.type = '").append(EClassType.LC)
				.append("' ");

		SQLQuery query5 = this.getSession().createSQLQuery(sql5.toString());
		List list5 = query5.list();
		BigDecimal c5 = (BigDecimal) list5.get(0);
		int count5 = c5.intValue();
		countResult.setLfcount(count5);

		StringBuilder sql6 = new StringBuilder();
		sql6
				.append("SELECT COUNT(*) FROM ef_leadsclassentity h, ef_classentity e,ef_teacher t,ef_classtype k WHERE  ");
		sql6
				.append(" e.status='VALID' AND e.teacher_id = t.id  AND e.classtype_id = k.id AND h.classentity_id = e.id AND ");
		sql6
				.append(
						" h.classStatus = 'CHECKIN' AND  to_char(e.classDate,'yyyy-mm-dd') = '")
				.append(formater.format(today)).append("'    ");
		sql6.append(" AND e.classTime = '").append(classTime.name()).append(
				"' AND ");
		sql6.append(" t.foreign = 0  AND k.type = '").append(EClassType.LC)
				.append("' ");

		SQLQuery query6 = this.getSession().createSQLQuery(sql6.toString());
		List list6 = query6.list();
		BigDecimal c6 = (BigDecimal) list6.get(0);
		int count6 = c6.intValue();
		countResult.setLccount(count6);

		countResult.setToday(today);
		countResult.setTime(classTime);

		return countResult;
	}
}
